package mysqlBenchmark;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.Date;

import com.mysql.jdbc.PreparedStatement;

public class Benchmark {

	public static void main(String[] args) {
		Connection connection;
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://172.20.46.89:3306/xiayuhang5?characterEncoding=UTF-8";
		String user = "xiayuhang";
		String password = "hadoop";
		Benchmark benchmark = new Benchmark();
		try {
			Class.forName(driver);
//			connection = DriverManager.getConnection(url, user, password);
//			if (!connection.isClosed())
//				System.out.println("Succeeded connecting to the Database!");
//			benchmark.evaluateTimeCost(connection, "sql/huQuery.txt", "sql/huIgnore.txt", "sql/huResult.txt");
//			
//			url="jdbc:mysql://172.20.46.89:3306/spoBase?characterEncoding=UTF-8";
//			connection = DriverManager.getConnection(url, user, password);
//			if (!connection.isClosed())
//				System.out.println("Succeeded connecting to the Database!");
//			benchmark.evaluateTimeCost(connection, "sql/spoQuery.txt", "sql/spoIgnore.txt", "sql/spoResult.txt");
//			
			url="jdbc:mysql://172.20.46.89:3306/baseOnType?characterEncoding=UTF-8";
			connection = DriverManager.getConnection(url, user, password);
			if (!connection.isClosed())
				System.out.println("Succeeded connecting to the Database!");
			benchmark.evaluateTimeCost(connection, "sql/typeQuery.txt", "sql/typeIgnore.txt", "sql/typeResult.txt");
		} catch (Exception e) {
			e.printStackTrace();// TODO: handle exception
		}
	}

	// 每条SQL执行100次后取平均用时
	public void evaluateTimeCost(Connection connection, String sqlFilePath, String ignoreFilePath, String resultPath)
			throws Exception {
		BufferedReader sqlReader = new BufferedReader(
				new InputStreamReader(new FileInputStream(new File(sqlFilePath)), "UTF-8"));
		BufferedReader ignoreReader = new BufferedReader(
				new InputStreamReader(new FileInputStream(new File(ignoreFilePath)), "UTF-8"));
		BufferedWriter bWriter = new BufferedWriter(
				new OutputStreamWriter(new FileOutputStream(new File(resultPath)), "UTF-8"));
		String sql = "", ignore = "";
		String[] IGNORE = null;
		if ((ignore = ignoreReader.readLine()) != null)
			IGNORE = ignore.split(" ");
		int flag = 0;// 标志该SQL语句是否在ignore列表中
		Statement statement = connection.createStatement();
		while ((sql = sqlReader.readLine()) != null) {
			flag = 0;
			String[] items = sql.split("\t");
			if (IGNORE != null)
				for (String a : IGNORE) {
					if (a.equals(items[0]))
						flag = 1;
				}
			if (flag == 1)
				continue;
			else {
				Date begin = new Date();
				bWriter.write(items[0] + " " + begin.toString() + "\t");
				for (int i = 0; i < 100; i++) {
					// ResultSet rSet = statement.executeQuery(items[1]);
					PreparedStatement pStatement = (PreparedStatement) connection.prepareStatement(items[1]);
					pStatement.executeQuery();
					System.out.println(items[0] + i);
				}
				Date enDate = new Date();
				bWriter.write(new Date().toString() + "\t");
				long cost = enDate.getTime() - begin.getTime();
				double avgCost = ((cost + 0.0) / 1000) / 100;
				DecimalFormat dFormat = new DecimalFormat("##.000000");
				bWriter.write(dFormat.format(avgCost) + "\n");
			}
		}
		sqlReader.close();
		ignoreReader.close();
		bWriter.close();
	}
}
